package com.aaa.project.zhreport.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileTypeUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.aaa.common.exception.ServiceException;
import com.aaa.common.utils.SecurityUtils;
import com.aaa.common.utils.bean.BeanUtils;
import com.aaa.framework.security.LoginUser;
import com.aaa.project.file.service.ISysFileService;
import com.aaa.project.zhreport.controller.dto.YwfxDataTemplateConfigReqDTO;
import com.aaa.project.zhreport.controller.dto.YwfxDataTemplateConfigResDTO;
import com.aaa.project.zhreport.domain.YwfxDataSources;
import com.aaa.project.zhreport.domain.YwfxDataTemplateConfig;
import com.aaa.project.zhreport.domain.YwfxDataTemplateVariable;
import com.aaa.project.zhreport.enums.FileStatusEnum;
import com.aaa.project.zhreport.mapper.YwfxDataApplyMapper;
import com.aaa.project.zhreport.mapper.YwfxDataSourcesMapper;
import com.aaa.project.zhreport.mapper.YwfxDataTemplateConfigMapper;
import com.aaa.project.zhreport.mapper.YwfxDataTemplateVariableMapper;
import com.aaa.project.zhreport.service.ExcelParseService;
import com.aaa.project.zhreport.service.YwfxDataTemplateConfigService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.URI;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author baibin
 * @version 1.0
 * @date 2025/4/8 9:54
 * @description YwfxDataTemplateConfigServiceImpl
 */
@Service
public class YwfxDataTemplateConfigServiceImpl implements YwfxDataTemplateConfigService {

    private static final Logger log = LoggerFactory.getLogger(YwfxDataTemplateConfigServiceImpl.class);

    @Resource
    private ISysFileService sysFileService;

    @Resource
    private YwfxDataTemplateConfigMapper mapper;
    @Resource
    private YwfxDataApplyMapper ywfxDataApplyMapper;
    @Resource
    private YwfxDataTemplateVariableMapper variableMapper;
    @Resource
    private YwfxDataSourcesMapper dataSourceMapper;
    @Resource
    private YwfxDataTemplateConfigMapper ywfxDataTemplateConfigMapper;
    @Resource
    private ExcelParseService excelParseService;

    @Override
    public List<YwfxDataTemplateConfig> listPage(YwfxDataTemplateConfig query) {
        log.info("listPage query:{}", query);
        // 获取当前登录人
        // LoginUser loginUser = SecurityUtils.getLoginUser();
        // query.setCreateBy(loginUser.getUser().getUserName());
        return mapper.listPage(query);
    }

    @Override
    public YwfxDataTemplateConfigResDTO findDetailById(Long id) {
        return mapper.findDetailById(id);
    }

    @Transactional
    @Override
    public void add(YwfxDataTemplateConfigReqDTO request, HttpServletRequest httpRequest) {
        log.info("add request:{}", request);
        // 获取当前登录人
        LoginUser loginUser = SecurityUtils.getLoginUser();
        // 根据数据源id查询数据源
        YwfxDataSources dataSources = dataSourceMapper.findById(request.getDataSourceId());
        Assert.notNull(dataSources, "数据源不存在！");

        request.setCreateBy(loginUser.getUser().getUserName());
        request.setUpdateBy(loginUser.getUser().getUserName());
        YwfxDataTemplateConfig vo = new YwfxDataTemplateConfig();
        BeanUtils.copyBeanProp(vo, request);
        // 上传文件步骤
//        if (request.getAttachment() != null) {
//            checkFileSize(request.getAttachment().getSize());
//            vo.setAttachmentName(request.getAttachment().getOriginalFilename());
//            try {
//                // 读取 Excel 文件内容
//                List<List<Object>> rows = readTxtFromStream(request.getAttachment().getInputStream());
//                // 判断excel数据是否为空
//                if (CollUtil.isEmpty(rows) || rows.size() <= 1) {
//                    throw new ServiceException("上传的临时表Excel文件内容不能为空！");
//                }
//                excelParseService.parseExcelAndGenerateTable(rows, request.getTempTableName(), dataSources);
//            } catch (ServiceException e) {
//                String sql = generateDeleteSQL(vo.getTempTableName(), dataSources);
//                excelParseService.executeDDL(sql, dataSources); // 删除临时表
//                throw new ServiceException(e.getMessage());
//            } catch (Exception e) {
//                log.error("解析临时表Excel文件失败！", e);
//                throw new ServiceException("解析临时表Excel文件失败！");
//            }
//
//            String fileName = convertFileName(request.getAttachment().getOriginalFilename());
//            try {
//                String url = sysFileService.uploadFile(request.getAttachment().getInputStream(), fileName);
//                vo.setAttachmentPath(url);
//                vo.setTemplateName(request.getTemplateName());
//            } catch (Exception e) {
//                log.error("上传临时表附件失败！", e);
//                throw new ServiceException("上传临时表附件失败！");
//            }
//        }

//        if (request.getAttachment() != null) {
//            checkFileSize(request.getAttachment().getSize());
//            vo.setAttachmentName(request.getAttachment().getOriginalFilename());
            try {
                // 默认生成临时表
                excelParseService.parseExcelAndGenerateTable(request.getTempTableName(), dataSources);
            } catch (ServiceException e) {
                String sql = generateDeleteSQL(vo.getTempTableName(), dataSources);
                excelParseService.executeDDL(sql, dataSources); // 删除临时表
                throw new ServiceException(e.getMessage());
            } catch (Exception e) {
                log.error("解析临时表Excel文件失败！", e);
                throw new ServiceException("解析临时表Excel文件失败！");
            }

//            String fileName = convertFileName(request.getAttachment().getOriginalFilename());
//            try {
//                String url = sysFileService.uploadFile(request.getAttachment().getInputStream(), fileName);
//                vo.setAttachmentPath(url);
//                vo.setTemplateName(request.getTemplateName());
//            } catch (Exception e) {
//                log.error("上传临时表附件失败！", e);
//                throw new ServiceException("上传临时表附件失败！");
//            }
//        }
        // 上传字段注释文件
        if (request.getFieldAttachment() != null) {
            checkFileSize(request.getFieldAttachment().getSize());
            vo.setFieldAttachmentName(request.getFieldAttachment().getOriginalFilename());
            String fileName = convertFileName(request.getFieldAttachment().getOriginalFilename());
            try {
                String url = sysFileService.uploadFile(request.getFieldAttachment().getInputStream(), fileName);
                vo.setFieldAttachmentPath(url);
            } catch (Exception e) {
                log.error("上传字段注释附件失败！", e);
                throw new ServiceException("上传字段注释附件失败！");
            }
        }
        mapper.insert(vo);
        List<YwfxDataTemplateVariable> variables = convertVariableList(httpRequest, vo.getId(), loginUser);
        if (CollUtil.isNotEmpty(variables)) {
            variableMapper.batchInsert(variables);
        }
        log.info("add success id:{}", vo.getId());
    }

    /**
     * 判断文件大小
     *
     * @param size
     */
    private void checkFileSize(long size) {
        // 校验文件大小（限制为 5MB）
        long maxFileSize = 5 * 1024 * 1024; // 5MB
        if (size > maxFileSize) {
            throw new ServiceException("上传的文件大小不能超过 5MB！");
        }
    }

    /**
     * 读取 Excel 文件内容
     *
     * @param inputStream Excel 文件输入流
     * @return List<List<Object>> 文件内容（每行数据为一个 List<Object>）
     */
    private List<List<Object>> readExcelFromStream(InputStream inputStream) {
        // 创建 ExcelReader
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        // 读取所有行数据
        List<List<Object>> rows = reader.read();
        // 关闭资源
        reader.close();
        return rows;
    }

    private List<List<Object>> readTxtFromStream(InputStream inputStream) {
        List<List<Object>> rows = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                // 按逗号分割每一行
                String[] cells = line.split(",", -1);
                List<Object> row = new ArrayList<>();
                for (String cell : cells) {
                    row.add(cell.trim());
                }
                rows.add(row);
            }
        } catch (Exception e) {
            log.error("读取 TXT 文件失败！", e);
            throw new ServiceException("读取 TXT 文件失败！");
        }
        return rows;
    }

    /**
     * 转换文件名
     *
     * @param originalFilename
     * @return
     */
    private String convertFileName(String originalFilename) {
        String fileMainName = FileUtil.mainName(originalFilename);
        String fileExtension = FileUtil.extName(originalFilename);
        String fileName = StrUtil.format("{}_{}.{}", fileMainName, System.currentTimeMillis(), fileExtension);
        return fileName;
    }

    /**
     * 转换变量列表
     *
     * @param httpRequest
     * @param templateId
     * @param loginUser
     */
    private List<YwfxDataTemplateVariable> convertVariableList(HttpServletRequest httpRequest, Long templateId,
        LoginUser loginUser) {
        List<YwfxDataTemplateVariable> variables = new ArrayList<>();
        int index = 0;
        while (httpRequest.getParameter("variables[" + index + "][fieldName]") != null) {
            YwfxDataTemplateVariable variable = new YwfxDataTemplateVariable();
            String variableIdStr = httpRequest.getParameter("variables[" + index + "][variableId]");
            // 需要判断空字符的"null"
            if (StrUtil.isNotBlank(variableIdStr)) {
                variable.setId(Long.valueOf(variableIdStr));
            } else {
                variable.setId(null);
            }
            variable.setFieldName(httpRequest.getParameter("variables[" + index + "][fieldName]"));
            variable.setFieldType(httpRequest.getParameter("variables[" + index + "][fieldType]"));
            variable.setRequired(httpRequest.getParameter("variables[" + index + "][required]"));
            String remark = httpRequest.getParameter("variables[" + index + "][remark]");
            String variableRemark = httpRequest.getParameter("variables[" + index + "][variableRemark]");
            if (StrUtil.isNotBlank(remark)) {
                variable.setRemark(remark);
            } else if (StrUtil.isNotBlank(variableRemark)) {
                variable.setRemark(variableRemark);
            }

            variable.setTemplateId(templateId);
            variable.setCreateBy(loginUser.getUser().getUserName());
            variable.setUpdateBy(loginUser.getUser().getUserName());
            variables.add(variable);
            index++;
        }
        log.info("convertVariableList variables:{}", variables);
        return variables;
    }

    @Transactional
    @Override
    public void update(YwfxDataTemplateConfigReqDTO request, HttpServletRequest httpRequest) {
        log.info("update request:{}", request);
        // 获取当前登录人
        LoginUser loginUser = SecurityUtils.getLoginUser();
        request.setUpdateBy(loginUser.getUser().getUserName());
        // 校验该模板是否已被使用，若已被使用，则不允许修改
        Long count = ywfxDataApplyMapper.countByTemplateId(request.getId());
        if (count > 0) {
            throw new ServiceException("该模板正在审批的工单所使用，请审批完成后再修改！");
        }
        YwfxDataSources dataSources = dataSourceMapper.findById(request.getDataSourceId());
        Assert.notNull(dataSources, "数据源不存在！");

        YwfxDataTemplateConfig oldVo = ywfxDataTemplateConfigMapper.findById(request.getId());
        Assert.notNull(oldVo, "模板不存在！");
        YwfxDataTemplateConfig vo = new YwfxDataTemplateConfig();
        BeanUtils.copyBeanProp(vo, request);
        // 如果之前有附件，则先进行删除
        if (vo.getFieldAttachmentPath() != null) {
            // minio 删除文件
            sysFileService.deleteFile(vo.getFieldAttachmentPath());
        }
        if (StrUtil.isNotBlank(oldVo.getTempTableName())) {
            if (!oldVo.getTempTableName().equals(request.getTempTableName())) {
                // minio 删除文件
                sysFileService.deleteFile(oldVo.getAttachmentPath());
                // 删除临时表
                String sql = generateDeleteSQL(oldVo.getTempTableName(), dataSources);
                excelParseService.executeDDL(sql, dataSources);
                vo.setTempTableName(null);
                vo.setAttachmentPath(null);
                vo.setAttachmentName(null);
            }
        }

        // 修改了附件 需要进行上传
        String url = null;
        if (request.getAttachment() != null) {
            checkFileSize(request.getAttachment().getSize());
            vo.setAttachmentName(request.getAttachment().getOriginalFilename());
            vo.setTempTableName(request.getTempTableName());
            // 解析EXCEL文件，需要判断excel内容是否为空，根据入参的临时表名、以及解析excel表头作为临时表字段，
            // 然后根据dataSources的数据库类型字段dataSourceType(PGSQL/ORACLE)生成DDL语句，并执行，内容生成临时表数据
            try {
                // 读取 Excel 文件内容
                List<List<Object>> rows = readTxtFromStream(request.getAttachment().getInputStream());
                // 判断excel数据是否为空
                if (CollUtil.isEmpty(rows) || rows.size() <= 1) {
                    throw new ServiceException("上传的临时表Excel文件内容不能为空！");
                }
                excelParseService.parseExcelAndGenerateTable(rows, request.getTempTableName(), dataSources);
            } catch (ServiceException e) {
                throw new ServiceException(e.getMessage());
            } catch (Exception e) {
                log.error("解析临时表Excel文件失败！", e);
                throw new ServiceException("解析临时表Excel文件失败！");
            }

            try {
                String fileName = convertFileName(request.getAttachment().getOriginalFilename());
                url = sysFileService.uploadFile(request.getAttachment().getInputStream(), fileName);
                vo.setAttachmentPath(url);
            } catch (Exception e) {
                log.error("上传附件失败！", e);
                throw new ServiceException(e.getMessage());
            }
        }

        String fileUrl = null;
        if (request.getFieldAttachment() != null) {
            checkFileSize(request.getFieldAttachment().getSize());
            vo.setFieldAttachmentName(request.getFieldAttachment().getOriginalFilename());
            try {
                String fileName = convertFileName(request.getFieldAttachment().getOriginalFilename());
                fileUrl = sysFileService.uploadFile(request.getFieldAttachment().getInputStream(), fileName);
                vo.setFieldAttachmentPath(fileUrl);
            } catch (Exception e) {
                log.error("上传字段注释附件失败！", e);
                throw new ServiceException(e.getMessage());
            }
        }
        mapper.update(vo);
        processVariableUpdate(httpRequest, vo, loginUser);
        log.info("update success id:{}", vo.getId());
    }

    private String generateDeleteSQL(String tempTableName, YwfxDataSources dataSources) {
        String sql = StrUtil.format("DROP TABLE IF EXISTS {}", tempTableName);
        switch (dataSources.getDataSourceType()) {
            case "ORACLE":
                sql = StrUtil.format("DROP TABLE {}", tempTableName);
            default:
        }
        log.info("generateDeleteSQL sql:{}", sql);
        return sql;
    }


    /**
     * 处理变量修改
     *
     * @param httpRequest
     * @param vo
     * @param loginUser
     */
    private void processVariableUpdate(HttpServletRequest httpRequest, YwfxDataTemplateConfig vo, LoginUser loginUser) {
        // 获取原有变量id，然后与修改的变量做对比，差集进行删除，交集进行修改，新增的进行新增
        List<Long> oldVariables = variableMapper.findIdsByTemplateId(vo.getId());
        List<YwfxDataTemplateVariable> variables = convertVariableList(httpRequest, vo.getId(), loginUser);
        if (CollUtil.isNotEmpty(variables)) {
            // 新增的变量
            List<YwfxDataTemplateVariable> newVariables =
                variables.stream().filter(v -> v.getId() == null).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(newVariables)) {
                variableMapper.batchInsert(newVariables);
            }
            // 修改的变量
            List<YwfxDataTemplateVariable> updateVariables =
                variables.stream().filter(v -> v.getId() != null).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(updateVariables)) {
                variableMapper.batchUpdate(updateVariables);
            }
            // 删除的变量
            List<Long> deleteVariables = oldVariables.stream()
                .filter(v -> !variables.stream().filter(v2 -> v2.getId() != null).anyMatch(v2 -> v2.getId().equals(v)))
                .collect(Collectors.toList());
            if (CollUtil.isNotEmpty(deleteVariables)) {
                variableMapper.deleteByIds(deleteVariables);
            }
        } else {
            variableMapper.deleteByTemplateId(vo.getId());
        }
    }

    @Transactional
    @Override
    public void deleteById(Long id) {
        // 校验该模板是否已被使用，若已被使用，则不允许删除
        Long count = ywfxDataApplyMapper.countByTemplateId(id);
        if (count > 0) {
            throw new ServiceException("该模板已被使用，不允许删除！");
        }
        YwfxDataTemplateConfig vo = mapper.findById(id);
        Assert.notNull(vo, "模板不存在！");
        YwfxDataSources dataSources = dataSourceMapper.findById(vo.getDataSourceId());
        Assert.notNull(dataSources, "数据源不存在！");
        // 删除临时表
        if (StrUtil.isNotBlank(vo.getTempTableName())) {
            String sql = generateDeleteSQL(vo.getTempTableName(), dataSources);
            excelParseService.executeDDL(sql, dataSources);
        }
        // 删除模板
        mapper.deleteById(id);
        // 同时删除文件
        sysFileService.deleteFile(vo.getAttachmentPath());
    }

    @Transactional
    @Override
    public void enableOrDisable(Long id, String isEnable) {
        YwfxDataTemplateConfig vo = mapper.findById(id);
        if (!vo.getIsEnable().equals(isEnable)) {
            mapper.enableOrDisable(id, isEnable);
        }
    }

    @Override
    public void download(Long id, Integer type, HttpServletResponse response) {
        log.info("download id:{} type:{}", id, type);
        YwfxDataTemplateConfig vo = mapper.findById(id);
        if (vo == null) {
            throw new ServiceException("模板不存在！");
        }
        String fileName = null;
        String filePath = null;
        if (type == 1) {
            fileName = vo.getAttachmentName();
            filePath = vo.getAttachmentPath();
        } else if (type == 2) {
            fileName = vo.getFieldAttachmentName();
            filePath = vo.getFieldAttachmentPath();
        }
        // 根据fileUrl下载文件
        if (StrUtil.isBlank(filePath)) {
            throw new ServiceException("文件 URL 为空");
        }

        // 下载文件并写入响应
        InputStream inputStream = null;
        try {
            // 根据 fileUrl 获取文件资源
            // 修改服务器无法解析包含特殊字符的 URL
            URI uri = new URI(filePath.replace(" ", "%20")  // 手动替换空格
                            .replace("(", "%28")   // 编码左括号
                            .replace(")", "%29")   // 编码右括号
            );
            URL url = uri.toURL();
//            URL url = new URL(filePath);
            inputStream = url.openStream();
            // 设置响应头
            response.reset();
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + fileName + "\"");
            response.addHeader("Access-Control-Allow-Origin", "*");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setContentType("application/octet-stream; charset=UTF-8");
            // 将文件内容写入响应流
            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                response.getOutputStream().write(buffer, 0, bytesRead);
            }
            response.getOutputStream().flush();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("文件下载失败，错误信息：{}", e);
            throw new ServiceException("文件下载失败");
        }
    }
}
